﻿using Dapper;
using EOrm;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Threading.Tasks;

namespace LeanApp.Dao
{
    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public static class DbHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        internal static string DbConString
        {
            get
            {
                return EOrmCon.GetConString();
            }
        }

        /// <summary>
        /// 异步执行查询
        /// </summary>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>受影响的行</returns>
        public static async Task<int> ExecuteAsync(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteAsync(EOrmMap.GetSql(key), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 异步执行查询
        /// </summary>
        /// <typeparam name="T">返回值泛型</typeparam>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>查询的返回值</returns>
        public static async Task<object> ExecuteScalarAsync(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteScalarAsync(EOrmMap.GetSql(key), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 异步执行查询
        /// </summary>
        /// <typeparam name="T">返回值泛型</typeparam>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>查询的返回值</returns>
        public static async Task<T> ExecuteScalarAsync<T>(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteScalarAsync<T>(EOrmMap.GetSql(key), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 异步执行查询
        /// </summary>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>数据读取器</returns>
        public static async Task<IDataReader> ExecuteReaderAsync(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteReaderAsync(EOrmMap.GetSql(key), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 同步执行查询
        /// </summary>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>受影响的行</returns>
        public static int Execute(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Execute(EOrmMap.GetSql(key), pms);
            }
        }

        /// <summary>
        /// 同步执行查询
        /// </summary>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>查询的返回值</returns>
        public static object ExecuteScalar(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.ExecuteScalar(EOrmMap.GetSql(key), pms);
            }
        }

        /// <summary>
        /// 同步执行查询
        /// </summary>
        /// <typeparam name="T">返回值泛型</typeparam>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>查询的返回值</returns>
        public static T ExecuteScalar<T>(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.ExecuteScalar<T>(EOrmMap.GetSql(key), pms);
            }
        }

        /// <summary>
        /// 同步执行查询
        /// </summary>
        /// <param name="key">sql配置文件内的key值</param>
        /// <param name="pms">变量</param>
        /// <returns>数据读取器</returns>
        public static IDataReader ExecuteReader(string key, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.ExecuteReader(EOrmMap.GetSql(key), pms);
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static int Insert<T>(T model)
        {
            return Insert<T>(new List<T>() { model });
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static int Insert<T>(List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Execute(EOrmMap.Insert<T>(), modelList);
            }
        }
        /// <summary>
        /// 异步插入数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static async Task<int> InsertAsync<T>(T model)
        {
            return await InsertAsync<T>(new List<T>() { model }).ConfigureAwait(false);
        }

        /// <summary>
        /// 异步插入数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static async Task<int> InsertAsync<T>(List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteAsync(EOrmMap.Insert<T>(), modelList).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static int Update<T>(T model)
        {
            return Update<T>(new List<T>() { model });
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static int Update<T>(List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Execute(EOrmMap.Update<T>(), modelList);
            }
        }
        /// <summary>
        /// 异步更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="model">实体集合</param>
        /// <returns>影响行数</returns>
        public static async Task<int> UpdateAsync<T>(T model)
        {
            return await UpdateAsync<T>(new List<T>() { model }).ConfigureAwait(false);
        }

        /// <summary>
        /// 异步更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static async Task<int> UpdateAsync<T>(List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteAsync(EOrmMap.Update<T>(), modelList).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static int Update<T>(string wherePartial, T model)
        {
            return Update<T>(wherePartial, model);
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static int Update<T>(string wherePartial, List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Execute(EOrmMap.Update<T>(wherePartial), modelList);
            }
        }

        /// <summary>
        /// 异步更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static async Task<int> UpdateAsync<T>(string wherePartial, T model)
        {
            return await UpdateAsync<T>(wherePartial, model).ConfigureAwait(false);
        }
        /// <summary>
        /// 异步更新数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static async Task<int> UpdateAsync<T>(string wherePartial, List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteAsync(EOrmMap.Update<T>(wherePartial), modelList).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static int Delete<T>(T model)
        {
            return Delete<T>(new List<T>() { model });
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static int Delete<T>(List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Execute(EOrmMap.Delete<T>(), modelList);
            }
        }

        /// <summary>
        /// 异步删除数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="model">实体</param>
        /// <returns>影响行数</returns>
        public static async Task<int> DeleteAsync<T>(T model)
        {
            return await DeleteAsync<T>(new List<T>() { model }).ConfigureAwait(false);
        }

        /// <summary>
        /// 异步删除数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="modelList">实体集合</param>
        /// <returns>影响行数</returns>
        public static async Task<int> DeleteAsync<T>(List<T> modelList)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteAsync(EOrmMap.Delete<T>(), modelList).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="pms">变量</param>
        /// <returns>影响行数</returns>
        public static int Delete<T>(string wherePartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Execute(EOrmMap.Delete<T>(wherePartial), pms);
            }
        }

        /// <summary>
        /// 异步删除数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="pms">变量</param>
        /// <returns>影响行数</returns>
        public static async Task<int> DeleteAsync<T>(string wherePartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteAsync(EOrmMap.Delete<T>(wherePartial), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static IEnumerable<T> Select<T>(string wherePartial = null, string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Query<T>(EOrmMap.Select<T>(wherePartial, orderByPartial), pms);
            }
        }

        /// <summary>
        /// 异步查询数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static async Task<IEnumerable<T>> SelectAsync<T>(string wherePartial = null, string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.QueryAsync<T>(EOrmMap.Select<T>(wherePartial, orderByPartial), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 排序查询数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static IEnumerable<T> SelectOrderBy<T>(string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Query<T>(EOrmMap.Select<T>(orderByPartial), pms);
            }
        }
        /// <summary>
        /// 异步排序查询数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static async Task<IEnumerable<T>> SelectOrderByAsync<T>(string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.QueryAsync<T>(EOrmMap.SelectOrderBy<T>(orderByPartial), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static IEnumerable<T> All<T>(string wherePartial = null, string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Query<T>(EOrmMap.All<T>(wherePartial, orderByPartial), pms);
            }
        }
        /// <summary>
        /// 异步查询所有数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static async Task<IEnumerable<T>> AllAsync<T>(string wherePartial = null, string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.QueryAsync<T>(EOrmMap.All<T>(wherePartial, orderByPartial), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 排序查询所有数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static IEnumerable<T> AllOrderBy<T>(string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.Query<T>(EOrmMap.AllOrderBy<T>(orderByPartial), pms);
            }
        }

        /// <summary>
        /// 异步排序查询所有数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="orderByPartial">排序语句</param>
        /// <param name="pms">变量</param>
        /// <returns>实体集合</returns>
        public static async Task<IEnumerable<T>> AllOrderByAsync<T>(string orderByPartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.QueryAsync<T>(EOrmMap.AllOrderBy<T>(orderByPartial), pms).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 统计实体数量
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="pms">变量</param>
        /// <returns>统计结果</returns>
        public static int Count<T>(string wherePartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return dbCon.ExecuteScalar<int>(EOrmMap.Count<T>(wherePartial), pms);
            }
        }
        /// <summary>
        /// 异步统计实体数据
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="wherePartial">条件语句</param>
        /// <param name="pms">变量</param>
        /// <returns>统计结果</returns>
        public static async Task<int> CountAsync<T>(string wherePartial = null, Dictionary<string, object> pms = null)
        {
            using (IDbConnection dbCon = new SQLiteConnection(DbConString))
            {
                dbCon.Open();
                return await dbCon.ExecuteScalarAsync<int>(EOrmMap.Count<T>(wherePartial), pms).ConfigureAwait(false);
            }
        }
    }
}
